﻿<%@ WebHandler Language="C#" Class="CommonHandler" %>
using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/*
 * Description:通用查询方法
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * create Date:2022.02.16
 */

public class CommonHandler : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getProductFamily":
                        r = r = getProductFamily();//查询机型
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkcenter":
                        r = getWorkcenter();//查询工区
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryUsersByRoleName":
                        r = getFactoryUsersByRoleName(jObject);//根据角色查询分厂人员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryUsers":
                        r = getFactoryUsers(jObject);//查询分厂下所有员工
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getEmployeInfo":
                        r = getEmployeeInfo(jObject);//查询员工信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getRoleList":
                        r = getRoleList(jObject);//查询MES角色列表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTeam":
                        r = getTeam(jObject);//查询班组Team
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryInfo":
                        r = getFactoryInfo(jObject);//查询分厂信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询员工信息
    Result getEmployeeInfo(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT e.employeename,e.employeeid,e.fullname,
       f.factoryname,f.factoryid,
       wc.workcentername,wc.workcenterid,
       s.shiftname,nvl(e.shiftid,te.shiftid) shiftid,
       te.teamname,te.teamid,
       eroles.rolenames
FROM employee e 
LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid 
LEFT JOIN factory f ON f.factoryid = sv.factoryid 
LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid 
LEFT JOIN operation o ON o.operationid = sv.operationid
LEFT JOIN 
( 
  SELECT e.employeeid,listagg(r.rolename,'、') WITHIN GROUP (ORDER BY r.rolename) rolenames  
  FROM employeerole er 
  LEFT JOIN employee e ON e.employeeid = er.employeeid  
  LEFT JOIN roledef r ON r.roleid = er.roleid  
  GROUP BY e.employeeid 
) eroles ON eroles.employeeid = e.employeeid 
LEFT JOIN 
(
  SELECT  t.teamname,t.teamid,te.employeeid,t.shiftid 
  FROM teamemployee te 
  LEFT JOIN team t ON t.teamid = te.teamid 
) te  ON te.employeeid = e.employeeid AND ROWNUM = 1
LEFT JOIN shift s ON s.shiftid = nvl(e.shiftid,te.shiftid)
WHERE e.employeeid = '{0}'
";
        sql = string.Format(sql, jObject["employeeid"].ToString());
        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查机型
    Result getProductFamily()
    {
        Result r = new Result();
        DataTable dt = dal.GetDataTable("SELECT pf.productfamilyid,pf.productfamilyname FROM productfamily pf");
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询分厂及工区信息
    Result getWorkcenter()
    {
        Result r = new Result();
        string sql = @"
SELECT f.factoryid,f.factoryname,w.workcenterid,w.workcentername
FROM factory f 
LEFT JOIN Workcenter w ON w.factoryid = f.factoryid
WHERE w.factoryid IS NOT NULL AND w.notes IS NULL 
ORDER BY f.factoryid
";
        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //根据角色查询分厂人员
    Result getFactoryUsersByRoleName(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT DISTINCT e.employeename,e.employeeid,e.fullname
FROM roledef rd
LEFT JOIN employeerole er ON er.roleid = rd.roleid
LEFT JOIN employee e ON e.employeeid = er.employeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @" 
LEFT JOIN mes_factory_mapping f ON f.factoryid = sv.factoryid";
        }
        else
        {
            sql += @"
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'";
        }
        sql += @"
WHERE e.fullname IS NOT NULL  
";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["factorycode"] != null && jObject["factorycode"].ToString() != "")
        {
            if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
            {
                sql += string.Format(" AND f.factorycode = '{0}'", jObject["factorycode"].ToString());
            }
            else
            {
                sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factorycode"].ToString());
            }
        }

        if (jObject["rolename"] != null && jObject["rolename"].ToString() != "")
        {
            string rolename = jObject["rolename"].ToString();
            if (rolename.IndexOf('|') != -1)
            {
                string[] list = rolename.Split('|');
                string roles = "(";
                foreach (var i in list)
                {
                    roles += "'" + i + "',";
                }
                roles = roles.TrimEnd(',');
                roles += ")";
                sql += string.Format(" AND rd.rolename in {0}", roles);
            }
            else
            {
                sql += string.Format(" AND rd.rolename = '{0}'", jObject["rolename"].ToString());
            }
        }

        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询分厂员工
    Result getFactoryUsers(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT e.employeename,e.employeeid,e.fullname
FROM employee e
LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @" 
LEFT JOIN mes_factory_mapping f ON f.factoryid = sv.factoryid";
        }
        else
        {
            sql += @"
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'";
        }
        sql += @"
WHERE 1=1 
";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["factorycode"] != null && jObject["factorycode"].ToString() != "")
        {
            if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
            {
                sql += string.Format(" AND f.factorycode = '{0}'", jObject["factorycode"].ToString());
            }
            else
            {
                sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factorycode"].ToString());
            }
        }

        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询MES角色列表
    Result getRoleList(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT rd.rolename
FROM roledef rd
WHERE rd.rolename NOT LIKE '%Default%' AND rd.rolename NOT IN ('SPC','Portal Configuration','Mfg Audit Trail Inquiry','Security Administration','Login')";
        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询
    Result getTeam(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.teamid,t.teamname
FROM team t
LEFT JOIN Workcenter w ON w.workcenterid = t.workcenterid";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @" 
LEFT JOIN mes_factory_mapping f ON f.factoryid = w.factoryid";
        }
        else
        {
            sql += @"
LEFT JOIN factory f ON f.factoryid = w.factoryid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'";
        }
        sql += @"
WHERE t.teamid IS NOT NULL ";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND w.workcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND w.workcentername = '{0}'", jObject["workcentername"].ToString());
        }

        if (jObject["factorycode"] != null && jObject["factorycode"].ToString() != "")
        {
            if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
            {
                sql += string.Format(" AND f.factorycode = '{0}'", jObject["factorycode"].ToString());
            }
            else
            {
                sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factorycode"].ToString());
            }
        }

        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询分厂信息
    Result getFactoryInfo(JObject jObject)
    {
        string sql = "";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql = "SELECT f.factoryid,f.factoryname,f.factorycode FROM mes_factory_mapping f WHERE 1=1 ";
        }
        else
        {
            sql = @"
SELECT f.factoryid,f.factoryname,u.attributevalue factorycode
FROM factory f
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
WHERE 1=1 ";
        }

        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["factorycode"] != null && jObject["factorycode"].ToString() != "")
        {
            if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
            {
                sql += string.Format(" AND f.factorycode = '{0}'", jObject["factorycode"].ToString());
            }
            else
            {
                sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factorycode"].ToString());
            }
        }
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}